package dao;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URI;
import java.net.URL;
import java.net.URLClassLoader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.jar.JarEntry;
import java.util.jar.JarFile;

import beans.MySQL;

public class MySql implements MySQL
{
	/**
	 * @param  databaseConfig 数据库参数
	 */
	private static Map<String, String> databaseConfig = null;
	
	/**
	 *@param conn 当前连接 
	 */
	private static Connection conn = null;
	
	private static MySql mySQL = null;
	
	private String key[] = new String[]{"driver", "url", "name", "pwd"};
	
	protected List <Map<String, Object>> selectParam = null;
	
	public static MySql getMySQL() {
		return mySQL;
	}

	public static void setMySQL(MySql mySQL) {
		MySql.mySQL = mySQL;
	}

	public List<Map<String, Object>> getSelectParam() {
		return selectParam;
	}
	
	/**
     * 不定条件参数查询
     */
	public void setSelectParam(List<Map<String, Object>> selectParam) {
		this.selectParam = selectParam;
	}

	public static Statement getStatement() {
		return statement;
	}

	public static void setStatement(Statement statement) {
		MySql.statement = statement;
	}


	protected static Statement statement = null;
	
	public Map<String, String> getDatabaseConfig() 
	{
		return databaseConfig;
	}

	public void setDatabaseConfig(Map<String, String> databaseConfig) 
	{
		MySql.databaseConfig = databaseConfig;
	}
	
	private MySql(Properties prop, String configPath)
	{
		Map<String, String> config = this.getDbConfig(prop, configPath);
		this.setDatabaseConfig(config);
	}
	
	public static MySql getInitance(Properties prop, String configPath)
	{
		if(MySql.mySQL == null) {
			MySql.mySQL = new MySql(prop, configPath);
		}
		return MySql.mySQL;
	}
	
	public static MySql getInitance(String configPath)
	{
		if(MySql.mySQL == null) {
			MySql.mySQL = new MySql(new Properties(), configPath);
		}
		return MySql.mySQL;
	}
	
	/**
	 * 链接数据库 
	 * @param driverPath 驱动路径包
	 */
	public Object connectMySQL(String driverPath, String masterJar)
	{
		File file = new File(driverPath);
		//将文件对象 映射为url对象
		URI uri = file.toURI();
		Object obj = null;
		// 实例化类加载器
		try {
			@SuppressWarnings("resource")
			URLClassLoader loader = new URLClassLoader(new URL[]{uri.toURL()});
			//实例化jar文件操作类
			@SuppressWarnings("resource")
			JarFile jarFile = new JarFile(file);
			Enumeration<JarEntry> enus = jarFile.entries();
			JarEntry jarEntry = null;
			String className,flg = null;
			while (enus.hasMoreElements()) {
				jarEntry = enus.nextElement();
				flg = jarEntry.getName();
				if (flg.endsWith(".class") && flg.equals(masterJar)) {
					//替换类文件名
					className = jarEntry.getName().replace(".class", "").replace("/", ".");
					obj		  = loader.loadClass(className).newInstance();
				}
			}
			if(obj == null) {
				throw new Exception("驱动未加载");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return obj;
	}
	
	@SuppressWarnings("static-access")
	protected Statement connect(Map<String, String> databaseConfig)
	{
		if(databaseConfig == null) {
			return null;
		}
		if(MySql.statement != null && (MySql.statement instanceof Statement) )
		{
			return MySql.statement;
		}
		//检测键值对应
		int i = 0;
		int length = databaseConfig.size();
		for(Map.Entry<String, String> db : databaseConfig.entrySet())
		{
			if(length > i && i < this.key.length) {
				 if(this.key[i].valueOf(db.getKey()) == null) {
					return null;
				 }
				 i++;
			} else {
				break;
			}
		}
		try {
			Class.forName(databaseConfig.get("driver"));
			java.sql.Connection connect = DriverManager.getConnection(databaseConfig.get("url"), databaseConfig.get("username"), databaseConfig.get("password"));
			
			statement = connect.createStatement();
			conn = connect;
			return MySql.statement;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	

	public boolean fullConnect() throws SQLException
	{
		if(conn != null) {
			conn.close();
			return true;
		} else {
			return false;
		}
	}
	
	public boolean fullStmt() throws SQLException
	{
		if(statement != null) {
			statement.close();
			return true;
		} else {
			return false;
		}
	}
	
	/**
	 * 获取配置 
	 */
	protected Map<String, String> getDbConfig( Properties prop, String filePath)
	{
		if(!(prop instanceof Properties)) {
			return null;
		}
		if(MySql.databaseConfig != null)
		{
			return MySql.databaseConfig;
		}
		//获取配置
		InputStream in = this.getClass().getResourceAsStream(filePath);
		Map<String , String> map = new HashMap<String , String>();
		try {
			prop.load(in);
			Enumeration<?> en = prop.propertyNames(); //得到配置文件的名字
			String strKey, strValue;
	        while(en.hasMoreElements()) {
	           strKey = (String) en.nextElement();
	           strValue = prop.getProperty(strKey);
	           map.put(strKey, strValue);
	       }
	       MySql.databaseConfig = map;
	       return MySql.databaseConfig;
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return MySql.databaseConfig;
	}
	
	
	 /** 
     * 更新数据库操作 
     *  
     * @param sql 
     * @param args 
     */  
	public int update(String sql, Object... args) {  
        
		PreparedStatement preparedStatement = null;  
       
		try {  
            preparedStatement = MySql.conn.prepareStatement(sql);  
  
            for (int i = 0; i < args.length; i++) {  
                preparedStatement.setObject(i + 1, args[i]);  
            }  
           int i = preparedStatement.executeUpdate();  
           return i;
        } catch (Exception e) {  
            e.printStackTrace();  
        }
        return 0;
    }  
	
	/**
	 * 执行SQL语句 
	 */
	public List<String> parseData(String sql, Map<String, String> databaseConfig)
	{
		//获取数据库执行对象
		java.sql.Statement state = this.connect(databaseConfig);
		List<String> listData = new ArrayList<String>();
		try {
			ResultSet result =  state.executeQuery(sql);
			listData = this.handleResultSetToMapList(result);
			return listData;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	
	/** 
     *  
     * @param clazz 
     * @param values 
     * @return 
     * @throws InstantiationException 
     * @throws IllegalAccessException 
     * @throws InvocationTargetException 
     */  
//    public <T> List<T> transfterMapListToBeanList(Class<T> clazz,  
//            List<Map<String, Object>> values) throws InstantiationException,  
//            IllegalAccessException, InvocationTargetException {  
//  
//        List<T> result = new ArrayList<T>();  
//  
//        T bean = null;  
//  
//        if (values.size() > 0) {  
//            for (Map<String, Object> m : values) {  
//                bean = clazz.newInstance();  
//                for (Map.Entry<String, Object> entry : m.entrySet()) {  
//                    String propertyName = entry.getKey();  
//                    Object value = entry.getValue();  
//  
////                    BeanUtils.setProperty(bean, propertyName, value);  
//                }  
//                // 13. 把 Object 对象放入到 list 中.  
//                result.add(bean);  
//            }  
//        }  
//  
//        return result;  
//    }  
	
	 /** 
     *  
     * @param resultSet 
     * @return 
     * @throws SQLException 
     */  
    public  List<String> handleResultSetToMapList( ResultSet resultSet) throws SQLException {  
//        List<Map<String, Object>> values = new ArrayList<Map<String, Object>>();  
//        List<String> columnLabels = this.getColumnLabels(resultSet);  
//        Map<String, Object> map = new HashMap<String, Object>(); 
//        Object value = null;
        List<String> list=new ArrayList<String>();//创建取结果的列表，之所以使用列表，不用数组，因为现在还不知道结果有多少，不能确定数组长度，所有先用list接收，然后转为数组  
        while (resultSet.next()) {
        	for(int i = 0; i < resultSet.getMetaData().getColumnCount(); i++)
        	{
        		list.add(resultSet.getString(i+1));
        	}
        }  
        
//        while (resultSet.next()) {  
//            for (String columnLabel : columnLabels) {  
//               value = resultSet.getObject(columnLabel);  
//               System.out.println(value);
//               map.put(columnLabel, value);  
//            }  
//            values.add(map);  
//        }  
        return list;  
    }  
    
    /**
     * 查询数据库
     * @param sql  sql 语句
     * @return {@link List}
     */
    public <T> List<Object> query( String sql, Object[] args) throws Exception {
		if(MySql.conn == null)
		{
			throw new Exception("未连接数据");
		}
    	List<Object> result = new ArrayList<Object>();
		PreparedStatement pst = conn.prepareStatement(sql);
		ResultSet rs = pst.executeQuery();
		int colAmount = rs.getMetaData().getColumnCount();
		if (colAmount > 1) {
			Object[] temp = null;
			while (rs.next()) {
				temp = new Object[colAmount];
				for (int i=0; i<colAmount; i++) {
					temp[i] = rs.getObject(i + 1);
				}
				result.add(temp);
			}
		}
		else if(colAmount == 1) {
			while (rs.next()) {
				result.add(rs.getObject(1));
			}
		}
		return result;
	}
    
    @SuppressWarnings("null")
	public static List<?> query(List<Map<String, Object>> params) throws SQLException{
        //获取数据库连接
        List<?> usersList = new ArrayList<Object>();
        //通过拼接构建SQL语句
        StringBuilder sb = new StringBuilder();
        sb.append("select * from users where 1=1 ");
        if(params!=null&&params.size()>0){
        	Map<String, Object> map  = null;
            for(Map<String, Object> param : params){
            	if(param != null)
            	{
            		for(Map.Entry<String, Object> ps : map.entrySet())
            		{
            			sb.append(" and "+ps.getKey()+" "+map.get("rela")+" "+map.get("value")+" ");
            		}
            	}
            }
        }
        //执行SQL语句
        PreparedStatement ps =conn.prepareStatement(sb.toString()); 
        System.out.println(sb.toString());
        ResultSet rs = ps.executeQuery();
        //提取查询结果
       
        return usersList;
    }
    
    
    /**
     * 获取数据库字段 
     */
    
    public List<String> getField(String tableName, String databaseName)
    {
		return this.parseData("select COLUMN_NAME from information_schema.COLUMNS where table_name = '"+tableName+"' and table_schema = '"+databaseName+"'", this.getDatabaseConfig());
    }
    
    
	/**
	 * 获取键 
	 */
    public Map<String, String> getKey(String sql, Map<String, String> dbConfig)
    {
		return dbConfig;
    }

    public String[] getKey() {
		return key;
	}

	public void setKey(String key[]) {
		this.key = key;
	}

	public static Connection getConn() {
		return conn;
	}

	public static void setConn(Connection conn) {
		MySql.conn = conn;
	}

	@Override
	public boolean delete(String sql, Object[] args) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public int insert(String sql) {
		// TODO Auto-generated method stub
		return 0;
	}


	@Override
	public boolean delete(String sql) {
		// TODO Auto-generated method stub
		return false;
	}
	
}
